﻿using System;
using System.Collections.Generic;
using System.Xml;
using System.Web;
using System.IO;
using System.Security.Cryptography;
using System.Data.SqlClient;
using System.Net.Mail;

namespace comix
{
    /// <summary>
    /// Summary description for $codebehindclassname$
    /// </summary>
    public class rps : IHttpHandler
    {
        private Dictionary<string, string> parameters;

        const int UserNameAvailabe = 1;
        const int RegisterUser = 2;
        const int GetPendingBooks = 3;
        const int RegisterAnonumousUser = 4;
        const int RemindPassword = 5;
        const int GetBooksOfUser = 6;
        const int DeleteSampleOfUser = 7;
        const int RateBook = 10;
        const int StoreTransaction = 11;
        const int GetFileSize = 12;
        const int ClearPending = 13;
        const int GetTransactions = 14;
        const int SetUserData = 16;
        const int ReRegisterUser = 17;
        const int GetOutOfSynchBooks = 18;
        const int GetUserDiscount = 19;
        const int InvalidateBook = 22;
        const int StoreDemo = 25;
        const int IsAlreadyBought = 26;
        const int SynchronizeAll = 28;
        const int StoreSample = 29;
        const int AddAPNToken = 30;
     //   const int SetSeriesNotification = 29;

        public void ProcessRequest(HttpContext context)
        {
            try
            {
                StreamReader sr1 = new StreamReader(context.Request.InputStream);
                string s = sr1.ReadToEnd();
                parameters = new Dictionary<string, string>();
                int opcode = Convert.ToInt32(context.Request.QueryString["op"]);
                if (s != null && s != "")
                {
                    string dec = "";
                    if (opcode < 1000)
                    {
                        s = s.Replace("|", "/");
                        s = s.Replace("!", "+");
                        byte[] edata = Convert.FromBase64String(s);
                        byte[] key = { 120, 17, 39, 66, 1, 67, 139, 11, 68, 100, 97, 217, 200, 43, 253, 149 };
                        Rijndael rij = Rijndael.Create();
                        rij.Padding = PaddingMode.None;
                        rij.Mode = CipherMode.CBC;
                        rij.Key = key;
                        byte[] iv = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
                        rij.IV = iv;
                        MemoryStream ms = new MemoryStream(edata);
                        CryptoStream csDecrypted = new CryptoStream(ms, rij.CreateDecryptor(), CryptoStreamMode.Read);
                        StreamReader sr = new StreamReader(csDecrypted);
                        dec = sr.ReadToEnd();
                        parameters.Add("original", dec);
                        sr.Close();
                        ms.Close();
                        dec = dec.Replace("\0", "");
                        dec = dec.Trim();
                    }
                    else
                    {
                        opcode -= 1000;
                        dec = s.Trim();
                    }
                    char[] sep = { '&' };
                   // char[] sep1 = { '=' };
                    string[] parts = dec.Split(sep);
                    foreach (string p in parts)
                    {
                        int k = p.IndexOf('=');
                        if (k > -1)
                        {
                            string ky = p.Substring(0, k);
                            string val = p.Substring(k + 1);
                            parameters.Add(ky, val);
                            //string[] subparts = p.Split(sep1);
                            //if (subparts.Length == 2)
                            //    parameters.Add(subparts[0], subparts[1]);
                        }
                    }
                }
                switch (opcode)
                {
                    case UserNameAvailabe:
                        DoUserNameAvailable(context);
                        break;
                    case RegisterUser:
                        DoRegisterUser(context);
                        break;
                    case ReRegisterUser:
                        DoReRegisterUser(context);
                        break;
                    case GetBooksOfUser:
                        DoGetBooksOfUser(context);
                        break;
                    case RemindPassword:
                        DoRemindPasswod(context);
                        break;
                    case RegisterAnonumousUser:
                        DoRegisterAnonumousUser(context);
                        break;
                    case DeleteSampleOfUser:
                        DoDeleteSampleOfUser(context);
                        break;
                    case GetPendingBooks:
                        DoGetPendingBooks(context);
                        break;
                    case SetUserData:
                        DoSetUserData(context);
                        break;
                    case RateBook:
                        DoRateBook(context);
                        break;
                    case GetFileSize:
                        DoGetFileSize(context);
                        break;
                    case ClearPending:
                        DoClearPending(context);
                        break;
                    case GetTransactions:
                        DoGetTransactions(context);
                        break;
                    case GetUserDiscount:
                        DoGetUserDiscount(context);
                        break;
                    case InvalidateBook:
                        //DoInvalidateBook(context);
                        break;
                    case StoreDemo:
                        //DoStoreDemo(context);
                        break;
                    case IsAlreadyBought:
                        //DoIsAlreadyBought(context);
                        break;
                    case SynchronizeAll:
                        //DoSynchronizeAll(context);
                        break;
                    case StoreTransaction:
                        DoStoreTransaction(context);
                        break;
                    case StoreSample:
                        DoStoreSample(context);
                        break;
                    case AddAPNToken:
                        DoAddAPNToken(context);
                        break;
                  /*  case SetSeriesNotification:
                        DoSetSeriesNotification(context);
                        break;*/
                }
            }
            catch (Exception e)
            {
                XmlDocument doc = RpHelper.PreparePlistXML();
                XmlElement dict = doc.CreateElement("dict");
                XmlElement array = doc.CreateElement("array");
                doc.DocumentElement.AppendChild(array);
                array.AppendChild(dict);
                XmlElement key = doc.CreateElement("key");
                XmlText txkey = doc.CreateTextNode("result");
                key.AppendChild(txkey);
                dict.AppendChild(key);
                XmlElement val = doc.CreateElement("string");
                XmlText txval = doc.CreateTextNode(e.Message+" "+parameters["original"]);
                val.AppendChild(txval);
                dict.AppendChild(val);
                context.Response.Write(doc.OuterXml);
                HttpContext.Current.ApplicationInstance.CompleteRequest();
            }
        }

  /*      private void DoSetSeriesNotification(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            if (parameters["p3"] == "0")
                com.CommandText = "DELETE USERS2SERIESNOTIFICATIONS WHERE u2s_usr_id = @usrid AND u2s_ser_id = @serid";
            else
            {
                com.CommandText = "DECLARE @n int; SELECT @n = (SELECT COUNT(*) FROM USERS2SERIESNOTIFICATIONS WHERE u2s_usr_id = @usrid AND u2s_ser_id = @serid; ";
                com.CommandText += "IF @n = 0 INSERT USERS2SERIESNOTIFICATIONS (u2s_usr_id, u2s_ser_id) VALUES (@u2s_usr_id, @u2s_ser_id)";
            }
            com.Parameters.Add(new SqlParameter("@usrid", Convert.ToInt32(parameters["p1"])));
            com.Parameters.Add(new SqlParameter("@serid", Convert.ToInt32(parameters["p2"])));
            RpHelper.SqlNonQueryToList(com, context.Response);
        }*/

        private void DoGetTransactions(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "SELECT CASE LEFT(trs_id,4) WHEN 'free' THEN 'free' ELSE trs_id END as trsid, trs_date, trs_price, bok_title from TRANSACTIONS INNER JOIN BOOKS on trs_bok_id = bok_id WHERE trs_usr_id = @usrid ORDER BY trs_date desc";
            com.Parameters.Add(new SqlParameter("@usrid", parameters["p1"]));
            RpHelper.SimpleResponse(com, context.Response);
        }

        private void DoRemindPasswod(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "SELECT usr_password FROM USERS WHERE usr_username = @usr_username";
            com.Parameters.Add(new SqlParameter("@usr_username", parameters["p1"]));
            string pwd = RpHelper.Sql2ScalarString(com);
            int r = 0;
            if (pwd != "")
            {
                MailMessage mm = new MailMessage("info@lcomix.com", parameters["p1"], "LΚομιξ. Υπενθύμιση κωδικού iPad βιβλιοπωλείου", "");
                mm.Body = "Ο κωδικός σας για το iPad βιβλιοπωλείο μας είναι: " + pwd;
                mm.BodyEncoding = System.Text.Encoding.UTF8;
                SmtpClient c = new SmtpClient("mail.lotus.arvixe.com", 26);
                System.Net.NetworkCredential cred = new System.Net.NetworkCredential("info@lcomix.com", "comix");
                c.Credentials = cred;
                c.DeliveryMethod = SmtpDeliveryMethod.Network;
                c.Send(mm);
            }
            else
            {
                r = -1;
            }

            XmlDocument doc = RpHelper.PreparePlistXML();
            XmlElement array = doc.CreateElement("array");
            doc.DocumentElement.AppendChild(array);
            XmlElement dict = doc.CreateElement("dict");
            array.AppendChild(dict);

            XmlElement key = doc.CreateElement("key");
            XmlText txkey = doc.CreateTextNode("result");
            key.AppendChild(txkey);
            dict.AppendChild(key);

            XmlElement val = doc.CreateElement("string");
            XmlText txval = doc.CreateTextNode(r.ToString());
            val.AppendChild(txval);
            dict.AppendChild(val);

            context.Response.Write(doc.OuterXml);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }

        private void DoGetUserDiscount(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "SELECT usr_special FROM USERS WHERE usr_id = @usrid";
            com.Parameters.Add(new SqlParameter("@usrid", parameters["p1"]));
            RpHelper.SimpleResponse(com, context.Response);
        }

        private void DoStoreSample(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "DECLARE @c int; SELECT @c = (SELECT COUNT(*) FROM BOOKS2USERS WHERE u2b_usr_id = @usrid AND u2b_bok_id = @bokid); ";
            com.CommandText += "IF @c=0 INSERT BOOKS2USERS (u2b_usr_id, u2b_bok_id, u2b_downloadcount, u2b_issample, u2b_pending) VALUES (@usrid,@bokid,0,1,1)";
            com.Parameters.Add(new SqlParameter("@usrid", parameters["p1"]));
            com.Parameters.Add(new SqlParameter("@bokid", parameters["p2"]));
            RpHelper.SqlNonQueryToList(com, context.Response);
        }

        private void DoGetBooksOfUser(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "SELECT u2b_bok_id, u2b_issample FROM BOOKS2USERS WHERE u2b_usr_id = @usrid";
            com.Parameters.Add(new SqlParameter("@usrid", parameters["p1"]));
            RpHelper.SimpleResponse(com, context.Response);
        }

        private void DoUserNameAvailable(HttpContext context)
        {
            BoolResult r = BLogic.IsUserNameavailable(parameters["p1"]);
            RpHelper.BoolResultResponse(r, context.Response);
        }

        private void DoRegisterAnonumousUser(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "SELECT usr_id FROM USERS WHERE usr_username = @usr_username";
            com.Parameters.Add(new SqlParameter("@usr_username", parameters["p1"]));
            string s = RpHelper.Sql2ScalarString(com);
            if (s == "")
            {
                RegisterNewUser(context, parameters["p1"], parameters["p1"], "dummy");
            }
            else
            {
                com.CommandText = "SELECT usr_code FROM USERS WHERE usr_username = @usr_username";
                string usrcode = RpHelper.Sql2ScalarString(com);
                IdResponse(context, s, usrcode, parameters["p1"],"",null);
            }
        }

        private void RegisterNewUser(HttpContext context, string username, string device, string password)
        {
            SqlCommand com = new SqlCommand();
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            Random r = new Random();
            for (int i = 0; i < 32; i++)
            {
                char k = (char)(r.Next(90) + 34);

                sb.Append(k);
            }
            com.CommandText = "DECLARE @adr_id int;INSERT ADDRESSES (adr_address) VALUES (''); SELECT @adr_id = (SCOPE_IDENTITY()); ";
            com.CommandText = "DECLARE @id int;INSERT USERS (usr_username, usr_registrationdate, usr_password, usr_code, usr_special, usr_notifications,usr_adr_id,usr_nlogins) VALUES (@usr_username, GetDate(), @usr_password, @usr_code,0,0,@adr_id,0);SELECT @id = (SCOPE_IDENTITY());INSERT USERDEVICES (usr_id, usr_device, usr_activation) VALUES (@id,@usr_device,GetDate()); SELECT @id as id";
            com.Parameters.Add(new SqlParameter("@usr_username", username));
            com.Parameters.Add(new SqlParameter("@usr_device", device));
            com.Parameters.Add(new SqlParameter("@usr_password", password));
            com.Parameters.Add(new SqlParameter("@usr_code", sb.ToString()));
            string s = RpHelper.Sql2ScalarString(com);

            IdResponse(context, s, sb.ToString(), device,"",null);

        }

        private void IdResponse(HttpContext context, string id, string unencryptedcode, string enckey, string errormessage, UserBookCollection ubc)
        {
            XmlDocument doc = RpHelper.PreparePlistXML();
            XmlElement array = doc.CreateElement("array");
            doc.DocumentElement.AppendChild(array);
            XmlElement dict = doc.CreateElement("dict");
            array.AppendChild(dict);

            XmlElement key = doc.CreateElement("key");
            XmlText txkey = doc.CreateTextNode("code");
            key.AppendChild(txkey);
            dict.AppendChild(key);

            XmlElement val = doc.CreateElement("string");
            XmlText txval = doc.CreateTextNode(RpHelper.EncryptedCode(unencryptedcode, enckey));
            val.AppendChild(txval);
            dict.AppendChild(val);

            key = doc.CreateElement("key");
            txkey = doc.CreateTextNode("id");
            key.AppendChild(txkey);
            dict.AppendChild(key);

            val = doc.CreateElement("string");
            txval = doc.CreateTextNode(id);
            val.AppendChild(txval);
            dict.AppendChild(val);

            key = doc.CreateElement("key");
            txkey = doc.CreateTextNode("message");
            key.AppendChild(txkey);
            dict.AppendChild(key);

            val = doc.CreateElement("string");
            txval = doc.CreateTextNode(errormessage);
            val.AppendChild(txval);
            dict.AppendChild(val);




            RpHelper.AddDictionaryKey("owned", doc, dict);
            XmlElement ownedarray = doc.CreateElement("array");
            dict.AppendChild(ownedarray);


            if (ubc != null)
            {
                foreach (UserBook ub in ubc.Items)
                {
                    string r = ub.bok_id.ToString() + ":" + (ub.isSample ? "1" : "0");
                    RpHelper.AddStringValue(r, doc, ownedarray);
                }
            }

         /*   int idd = Convert.ToInt32(id);
            if (idd > 0)
            {
                SqlConnection con = RpHelper.CreateConnection();
                SqlCommand com = new SqlCommand();
                com.Connection = con;
                com.CommandText = "SELECT u2b_bok_id, u2b_issample FROM BOOKS2USERS WHERE u2b_usr_id = @usrid";
                com.Parameters.Add(new SqlParameter("@usrid", idd));
                con.Open();
                SqlDataReader rdr = com.ExecuteReader();
                while (rdr.Read())
                {
                    string r = rdr.GetInt32(0).ToString() + ":" + (rdr.GetBoolean(1) ? "1" : "0");
                    RpHelper.AddStringValue(r, doc, ownedarray);
                }
                con.Close();
            }*/


            context.Response.Write(doc.OuterXml);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }

       

        private void DoRegisterUser(HttpContext context)
        {
            UserResult ur = BLogic.RegisterUser(parameters["p1"], parameters["p3"], parameters["p2"], 7, "", "", false, false, "", "", "", "", 1);
            IdResponse(context, ur.usrid.ToString(), ur.usrcode, parameters["p2"],ur.message,ur.books);
            /* SqlCommand com = new SqlCommand();
            com.CommandText = "SELECT usr_id FROM USERS WHERE usr_username = @usr_username AND usr_password ='dummy'";
            com.Parameters.Add(new SqlParameter("@usr_username", parameters["p2"]));
            string usrcode = RpHelper.Sql2ScalarString(com);
            if (usrcode == "")
                RegisterNewUser(context, parameters["p1"], parameters["p2"], parameters["p3"]);
            else
            {
                com.CommandText = "UPDATE USERS SET usr_username = @usr_username, usr_password = @usr_password WHERE usr_id = @usr_id";
                com.Parameters.Clear();
                com.Parameters.Add(new SqlParameter("@usr_username", parameters["p1"]));
                com.Parameters.Add(new SqlParameter("@usr_password", parameters["p3"]));
                com.Parameters.Add(new SqlParameter("@usr_id", Convert.ToInt32(usrcode)));

                SqlConnection con = RpHelper.CreateConnection();
                com.Connection = con;
                con.Open();
                com.ExecuteScalar();
                con.Close();

                com.Parameters.Clear();
                com.Parameters.Add(new SqlParameter("@usr_id", Convert.ToInt32(usrcode)));
                com.CommandText = "SELECT usr_code FROM USERS WHERE usr_id = @usr_id";
                string code = RpHelper.Sql2ScalarString(com);

                IdResponse(context, usrcode, code, parameters["p2"]);
            }*/
        }

        private void DoReRegisterUser(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "	DECLARE @usrid int;	DECLARE @usrcode char(32);DECLARE @n int;DECLARE @ntot int;";
            com.CommandText += " DECLARE @prevusrid int; SELECT @prevusrid = (SELECT usr_id FROM USERS WHERE usr_username = @usr_device AND usr_password = 'dummy'); ";
            com.CommandText += " SELECT @usrid = (SELECT usr_id FROM USERS WHERE usr_username = @usr_username AND usr_password = @usr_password);";
            com.CommandText += " IF(@usrid is NULL) SELECT 0,'' 	ELSE BEGIN ";
            com.CommandText += " SELECT @n = (SELECT COUNT(*) FROM USERDEVICES WHERE usr_id = @usrid AND usr_device = @usr_device);";
            com.CommandText += " SELECT @ntot =(SELECT COUNT(*) FROM USERDEVICES WHERE usr_id = @usrid AND usr_activation>DATEADD(year,-1,GetDate())) ";
            com.CommandText += " IF @n = 0	BEGIN IF (@ntot < 3) BEGIN ";
            com.CommandText += " INSERT USERDEVICES (usr_id, usr_device, usr_activation) VALUES (@usrid, @usr_device,GetDate());";
          
            com.CommandText += " IF(@prevusrid is not NULL) BEGIN ";
            com.CommandText += " UPDATE BOOKS2USERS SET u2b_usr_id = @usrid WHERE u2b_usr_id = @prevusrid AND u2b_bok_id NOT IN (SELECT u2b_bok_id FROM BOOKS2USERS WHERE u2b_usr_id = @usrid); ";
            com.CommandText += " UPDATE TRANSACTIONS SET trs_usr_id = @usrid WHERE trs_usr_id = @prevusrid; ";
            com.CommandText += " DELETE BOOKS2USERS WHERE u2b_usr_id = @prevusrid; ";
            com.CommandText += " DELETE USERDEVICES WHERE usr_id = @prevusrid; ";
            com.CommandText += " DELETE USERS WHERE usr_id = @prevusrid; ";
            com.CommandText += " END ";

            com.CommandText += " SELECT usr_id, usr_code FROM USERS WHERE usr_id = @usrid;";
            com.CommandText += " END ELSE SELECT -1,'' END ELSE BEGIN";

            com.CommandText += " IF(@prevusrid is not NULL) BEGIN ";
            com.CommandText += " UPDATE BOOKS2USERS SET u2b_usr_id = @usrid WHERE u2b_usr_id = @prevusrid AND u2b_bok_id NOT IN (SELECT u2b_bok_id FROM BOOKS2USERS WHERE u2b_usr_id = @usrid); ";
            com.CommandText += " UPDATE TRANSACTIONS SET trs_usr_id = @usrid WHERE trs_usr_id = @prevusrid; ";
            com.CommandText += " DELETE BOOKS2USERS WHERE u2b_usr_id = @prevusrid; ";
            com.CommandText += " DELETE USERDEVICES WHERE usr_id = @prevusrid; ";
            com.CommandText += " DELETE USERS WHERE usr_id = @prevusrid; ";
            com.CommandText += " END ";

            com.CommandText += " SELECT usr_id, usr_code FROM USERS WHERE usr_id = @usrid; END; END";


            com.Parameters.Add(new SqlParameter("@usr_username", parameters["p1"]));
            com.Parameters.Add(new SqlParameter("@usr_password", parameters["p2"]));
            com.Parameters.Add(new SqlParameter("@usr_device", parameters["p3"]));

            int id = 0;
            string code = "";
            SqlConnection con = RpHelper.CreateConnection();
            com.Connection = con;
            con.Open();
            SqlDataReader rdr = com.ExecuteReader();
            if (rdr.Read())
            {
                id = (int)rdr[0];
                code = (string)rdr[1];
            }
            con.Close();

            XmlDocument doc = RpHelper.PreparePlistXML();
            XmlElement array = doc.CreateElement("array");
            doc.DocumentElement.AppendChild(array);
            XmlElement dict = doc.CreateElement("dict");
            array.AppendChild(dict);


            string ecode = (code == "") ? "" : RpHelper.EncryptedCode(code, parameters["p3"]);
            RpHelper.AddDictionaryKey("code", doc, dict);
            RpHelper.AddStringValue(ecode, doc, dict);

            RpHelper.AddDictionaryKey("id", doc, dict);
            RpHelper.AddStringValue(id.ToString(), doc, dict);

            RpHelper.AddDictionaryKey("owned", doc, dict);
            XmlElement ownedarray = doc.CreateElement("array");
            dict.AppendChild(ownedarray);

            if (id > 0)
            {
                com.Parameters.Clear();
                com.CommandText = "SELECT u2b_bok_id, u2b_issample FROM BOOKS2USERS WHERE u2b_usr_id = @usrid";
                com.Parameters.Add(new SqlParameter("@usrid",id));
                con.Open();
                rdr = com.ExecuteReader();
                while (rdr.Read())
                {
                    string r = rdr.GetInt32(0).ToString() + ":" + (rdr.GetBoolean(1)?"1":"0");
                    RpHelper.AddStringValue(r, doc, ownedarray);
                }
                con.Close();
            }

            context.Response.Write(doc.OuterXml);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }

        private void DoDeleteSampleOfUser(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "DELETE books2users WHERE u2b_usr_id = @usr_id AND u2b_bok_id = @bok_id AND u2b_issample = 1";
            com.Parameters.Add(new SqlParameter("@usr_id", Convert.ToInt32(parameters["p1"])));
            com.Parameters.Add(new SqlParameter("@bok_id", Convert.ToInt32(parameters["p2"])));
            RpHelper.SqlNonQueryToList(com, context.Response);
        }

        private void DoSetUserData(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "UPDATE USERS SET usr_username = @username, usr_password = @usrpassword WHERE usr_id= @usrid";
            com.Parameters.Add(new SqlParameter("@usrid", Convert.ToInt32(parameters["p1"])));
            com.Parameters.Add(new SqlParameter("@username", parameters["p2"]));
            com.Parameters.Add(new SqlParameter("@usrpassword", parameters["p3"]));
            RpHelper.SqlNonQueryToList(com, context.Response);
        }

        private void DoStoreTransaction(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "DECLARE @n int;SELECT @n = (SELECT COUNT(*) FROM TRANSACTIONS WHERE trs_id = @trs_transactionid); ";
            com.CommandText += "IF @n>0 DELETE TRANSACTIONS WHERE trs_id = @trs_transactionid; ";
            com.CommandText += "DECLARE @price decimal(18,2); SELECT @price = (SELECT ptr_total FROM TIERS INNER JOIN BOOKS on ptr_id = bok_ptr_id WHERE bok_id = @trs_bok_id); ";
            com.CommandText += "DECLARE @netprice decimal(18,2); ";
            com.CommandText += "IF @trs_origin = 1 SELECT @netprice = (SELECT ptr_owned FROM TIERS INNER JOIN BOOKS on ptr_id = bok_ptr_id WHERE bok_id = @trs_bok_id) ";
            com.CommandText += "ELSE SELECT @netprice = (SELECT @price); ";
            com.CommandText += "IF LEFT(@trs_transactionid,4)= 'free' BEGIN SET @price = 0; SET @netprice = 0; END; ";
            com.CommandText += "INSERT TRANSACTIONS (trs_id, trs_usr_id, trs_bok_id, trs_date,trs_origin,trs_price, trs_netprice) VALUES (@trs_transactionid, @trs_usr_id, @trs_bok_id, GetDate(),@trs_origin,@price,@netprice); ";
            com.CommandText += "UPDATE BOOKS SET bok_sales = bok_sales + 1 WHERE bok_id = @trs_bok_id; ";
            com.CommandText += "DELETE BOOKS2USERS WHERE u2b_usr_id = @trs_usr_id AND u2b_bok_id = @trs_bok_id; ";
            com.CommandText += "INSERT BOOKS2USERS (u2b_usr_id,u2b_bok_id,u2b_downloadcount,u2b_pending,u2b_issample) ";
            com.CommandText += "VALUES (@trs_usr_id, @trs_bok_id,0,1,0);  ";
            com.CommandText += "SELECT @trs_transactionid as trsid";
            com.Parameters.Add(new SqlParameter("@trs_transactionid", parameters["p1"]));
            com.Parameters.Add(new SqlParameter("@trs_usr_id", Convert.ToInt32(parameters["p2"])));
            com.Parameters.Add(new SqlParameter("@trs_bok_id", parameters["p3"]));
            com.Parameters.Add(new SqlParameter("@trs_origin", Convert.ToByte(parameters["p4"])));
            RpHelper.SimpleResponse(com, context.Response);
        }

        private void DoGetPendingBooks(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "SELECT u2b_bok_id, u2b_issample FROM BOOKS2USERS WHERE u2b_usr_id = @usr_id AND u2b_pending = 1";
            com.Parameters.Add(new SqlParameter("@usr_id", parameters["p1"]));
            RpHelper.SimpleResponse(com, context.Response);
        }

        private void DoGetFileSize(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            int bokid = Convert.ToInt32(parameters["p1"]);
            com.CommandText = "SELECT CASE u2b_issample WHEN 1 THEN '1' ELSE '0' END FROM BOOKS2USERS WHERE u2b_bok_id = @bok_id AND u2b_usr_id = @usr_id";
            com.Parameters.Add(new SqlParameter("@bok_id", bokid ));
            com.Parameters.Add(new SqlParameter("@usr_id", Convert.ToInt32(parameters["p2"])));

            string issample = RpHelper.Sql2ScalarString(com);
            string filename =(issample=="0")?bokid.ToString()+".cmk":"s"+bokid.ToString()+".cmk";

            int sz = 0;
            if (filename != "")
            {
                FileInfo fi = new FileInfo(context.Request.MapPath("books/" + filename));
                sz = (int)fi.Length;
            }
            XmlDocument doc = RpHelper.PreparePlistXML();
            XmlElement array = doc.CreateElement("array");
            doc.DocumentElement.AppendChild(array);
            XmlElement dict = doc.CreateElement("dict");
            array.AppendChild(dict);

            XmlElement key = doc.CreateElement("key");
            XmlText txkey = doc.CreateTextNode("size");
            key.AppendChild(txkey);
            dict.AppendChild(key);

            XmlElement val = doc.CreateElement("string");
            XmlText txval = doc.CreateTextNode(sz.ToString());
            val.AppendChild(txval);
            dict.AppendChild(val);

            context.Response.Write(doc.OuterXml);
            HttpContext.Current.ApplicationInstance.CompleteRequest();  
        }

        private void DoRateBook(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "UPDATE BOOKS2USERS SET u2b_rate = @rate WHERE u2b_bok_id = @bokid AND u2b_usr_id = @usrid; ";
            com.CommandText += "DECLARE @n int; DECLARE @s int; DECLARE @r float; ";
            com.CommandText += "SELECT @n = (SELECT COUNT(*) FROM BOOKS2USERS WHERE u2b_bok_id = @bokid AND u2b_rate>0); ";
            com.CommandText += "SELECT @s = (SELECT SUM(u2b_rate) FROM BOOKS2USERS WHERE u2b_bok_id= @bokid AND u2b_rate>0); ";
            com.CommandText += "SET @r = CAST(@s AS float)/CAST(@n AS float); ";
            com.CommandText += "UPDATE BOOKS SET bok_nratings = @n, bok_rating = @r WHERE bok_id = @bokid; ";
            com.CommandText += "SELECT @bokid as bok_id, @r as bok_rating"; 
            com.Parameters.Add(new SqlParameter("@usrid", Convert.ToInt32(parameters["p1"])));
            com.Parameters.Add(new SqlParameter("@bokid", Convert.ToInt32(parameters["p2"])));
            com.Parameters.Add(new SqlParameter("@rate", Convert.ToInt32(parameters["p3"])));
            RpHelper.SimpleResponse(com, context.Response);
        }

        private void DoClearPending(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            if (parameters["p3"] == "1")
                com.CommandText = "UPDATE BOOKS2USERS SET u2b_downloaddate = GetDate(), u2b_downloadcount=u2b_downloadcount+1, u2b_pending = 0 WHERE u2b_usr_id = @usrid AND u2b_bok_id = @bokid";
            else
                com.CommandText = "UPDATE BOOKS2USERS SET u2b_pending = 0 WHERE u2b_usr_id = @usrid AND u2b_bok_id = @bokid";
            com.Parameters.Add(new SqlParameter("@usrid", Convert.ToInt32(parameters["p1"])));
            com.Parameters.Add(new SqlParameter("@bokid", Convert.ToInt32(parameters["p2"])));
            RpHelper.SqlNonQueryToList(com, context.Response);
        }

        private void DoAddAPNToken(HttpContext context)
        {
            SqlCommand com = new SqlCommand();
            com.CommandText = "DECLARE @n int; SELECT @n = (SELECT COUNT(*) FROM APNDevices WHERE devToken = @tok); IF @n = 0 INSERT APNDevices (devToken) VALUES (@tok)";
            com.Parameters.Add(new SqlParameter("@tok", parameters["p1"]));
            RpHelper.SqlNonQueryToList(com, context.Response);
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}
